package at.ac.tuwien.sepm.s2011.e0208329.db;
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.ResultSetMetaData;
import java.sql.SQLException; 
import java.sql.Statement;
import java.util.ArrayList;
//import java.util.HashMap;
//import java.util.Map;

import org.apache.log4j.Logger;
//import org.apache.log4j.PropertyConfigurator;

public class DatabaseManager {
	private static final Logger log = Logger.getLogger(DatabaseManager.class);
	public static DatabaseManager refDM;
	private Connection conn;
	
	private DatabaseManager() {
		try {
			Class.forName("org.hsqldb.jdbc.JDBCDriver" );
		} catch (Exception e) {
			log.error("Failed to load HSQLDB-JDBC driver.");
		}
		conn = openConnection();
		if (conn == null) {
			log.fatal("Unable to create connection to the database");
			System.exit(1);
		} else {
			log.info("Connection to the database was successfully created ! ");
		}
	}
	
    public Connection openConnection(){
		try{
			conn = DriverManager.getConnection(DBProperties.HOSTNAME, DBProperties.USERNAME, DBProperties.PASSWORD);
			return conn;
		} catch (SQLException e)  {
			log.error("Failed to open connection to database");
			return null;	    	     
	    } 
	}
    
	public Connection getConnection() {
		if (conn == null) {
			conn = openConnection();
		}
		return conn;
	}
    
    public void closeConnection() {
    	try {
        	Statement st = conn.createStatement();
        	st.execute("SHUTDOWN");
        	conn.close();      		
    	} catch (SQLException e)  {       
	    	e.printStackTrace();     
	    } 
    }
	
	public static DatabaseManager getDatabaseManager() {
	    if(refDM == null){
	        refDM = new DatabaseManager();
	    }
	    return refDM;
	}
	
	//use for SQL commands CREATE, DROP, INSERT and UPDATE
	public synchronized boolean update(String expression) throws SQLException {
		Statement st = null;
		st = conn.createStatement(); // statements
		int i = st.executeUpdate(expression); // run the query
		log.info("Staring SQL query = " + expression);
		if (i == -1) {
			log.error("Database error for the SQL statement " + expression);
			return false;
		}
		log.info("SQL update statement succeed: " + expression);
		st.close();
		return true;
	}
	
	//use for SQL command SELECT
    public synchronized  ArrayList<ArrayList<String>> query(String expression) throws SQLException {
        Statement st = null;
        ResultSet rs = null;
        st = conn.createStatement();         // statement objects can be reused with
        // repeated calls to execute but we
        // choose to make a new one each time
        rs = st.executeQuery(expression);    // run the query
        // do something with the result set.
        ArrayList<ArrayList<String>> rsArray = resultSetToArray(rs);
        //dump(rs);
        st.close();
        return rsArray;
        // NOTE!! if you close a statement the associated ResultSet is
        // closed too
        // so you should copy the contents to some other object.
        // the result set is invalidated also  if you recycle an Statement
        // and try to execute some other query before the result set has been
        // completely examined.
    }
	
	
/*	public static void main(String[] args) throws SQLException     
    {
		PropertyConfigurator.configure("log4j.properties");
		DatabaseManager dbm = DatabaseManager.getDatabaseManager(); 
		dbm.query("SELECT * FROM dienstleistung");
 	        
    }*/
	
	public static ArrayList<ArrayList<String>> resultSetToArray (ResultSet rs) throws SQLException {
        ResultSetMetaData meta   = rs.getMetaData();
        int colmax = meta.getColumnCount();
        int i;
        Object o = null;
        ArrayList<String> rsRow;
        ArrayList<ArrayList<String>> rsArray = new ArrayList<ArrayList<String>>(); 
        for (; rs.next(); ) {
        	rsRow = new ArrayList<String>();
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed with 1 not 0
                rsRow.add(o.toString()); 
            }
            rsArray.add(rsRow);
        }
        return rsArray;		
	}
	
    public static void dump(ResultSet rs) throws SQLException {
        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
        Object            o = null;

        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed

                // with 1 not 0
                System.out.print(o.toString() + " ");
            }

            System.out.println(" ");
        }
    } 
}
